# -*- coding: utf-8 -*-
import re
import sqlite3
import requests
import time
from datetime import datetime, timedelta


def cleanhtml(html):
    pattern = '\s+|(&nbsp;)+'
    # pattern='\s+'
    html = re.sub(pattern, ' ', html)
    # 去掉html标记间的空格
    # pattern='> <'
    # html=re.sub(pattern,'><',html)
    return html.replace('> <', '><')


def getfetch():
    querydatelist = gen_querydate(querydatecount)
    url = 'http://www.npedi.com/edi/cusmovAction.do?ctnNo=&s_VelInfo=&companyCode=&selectAll=on&beginOrderSendDate=%s&endOrderSendDate=%s' % (
        querydatelist[-1].replace('-', ''), querydatelist[0].replace('-', ''))
    print url
    res = requests.get(url)
    tds = cleanval(re.findall(
        '<td height="24" nowrap="nowrap" class="dblue12">(.*?)</td>', cleanhtml(res.text)))
    rows = []
    for i in range(len(tds) / 13):
        row = []
        for j in tds[i * 13 + 1:i * 13 + 13]:
            row.append(j)
        rows.append(row)
    return rows


def cleanval(v):
    if type(v) == list or type(v) == tuple:
        return [None if x.strip() == '' else x.strip() for x in v]
    else:
        return None if v.strip() == '' else v.strip()


def gen_querydate(datecount):
    querydatelist = []
    queryday = datetime.today()
    for c in xrange(datecount):
        querydatelist.append(datetime.strftime(queryday - timedelta(days=c), '%Y-%m-%d'))
    return querydatelist

# ##########################################
# 主程序开始
# ##########################################

head = ('vessel', 'voyage', 'boxno', 'pier', 'boxsize', 'movtype', 'h986', 'destplace', 'instructtime', 'movflag', 'dealtime', 'feedback')
querydatecount = 2

s = time.time()
rs = getfetch()
print 'fetch use second:', time.time() - s

s = time.time()
conn = sqlite3.connect('npedi.db3')
# conn.text_factory = str
# conn.row_factory = sqlite3.Row
querydatelist = gen_querydate(querydatecount)
sql = "delete from cusmov where instructtime>='%s 00:00:00' and instructtime<='%s 23:59:59'" % (
    querydatelist[-1], querydatelist[0])
conn.execute(sql)
sql = "delete from cusmov where instructtime<='%s 23:59:59'" % datetime.strftime(
    datetime.now() - timedelta(days=30), '%Y-%m-%d')
conn.execute(sql)
for r in rs:
    cols = ','.join(head)
    values = ','.join(['?'] * len(head))
    sql = 'insert into cusmov (%s) values(%s)' % (cols, values)
    conn.execute(sql, r)
conn.commit()
conn.close()
print 'write db use second:', time.time() - s
